﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace TEST_SCCM_MVC_01.Models
{
    public class CollectionRepository
    {
        private sms_celEntities sms = new sms_celEntities();

        public IQueryable<Collection> GetCollectionsByParent(string ParentID = "COLLROOT")
        {

            if (string.IsNullOrEmpty(ParentID))
            {
                throw new Exception("ParentID darf weder Leer noch Null sein!");
            }

            return (
                from col in sms.v_Collection
                join sub in sms.v_CollectToSubCollect on col.CollectionID equals sub.subCollectionID
                join counter in
                    (
                        from sub in sms.v_CollectToSubCollect
                        group sub by sub.parentCollectionID into newsub
                        select new
                        {
                            Key = newsub.Key,
                            Count = newsub.Count()
                        }
                    ) on col.CollectionID equals counter.Key into x
                from cols in x.DefaultIfEmpty()
                where sub.parentCollectionID == ParentID
                select new Collection
                {
                    ParentID = sub.parentCollectionID,
                    CollectionID = col.CollectionID,
                    Name = col.Name,
                    SubColCount = cols.Count == null ? 0 : cols.Count
                });
        }

        public IQueryable<Collection> GetAllCollections()
        {
            return (
                from col in sms.v_Collection
                join sub in sms.v_CollectToSubCollect on col.CollectionID equals sub.subCollectionID
                join counter in
                    (
                        from sub in sms.v_CollectToSubCollect
                        group sub by sub.parentCollectionID into newsub
                        select new
                        {
                            Key = newsub.Key,
                            Count = newsub.Count()
                        }
                    ) on col.CollectionID equals counter.Key into x
                from cols in x.DefaultIfEmpty()
                select new Collection
                {
                    ParentID = sub.parentCollectionID,
                    CollectionID = col.CollectionID,
                    Name = col.Name,
                    SubColCount = cols.Count == null ? 0 : cols.Count
                });
        }

        public System.Collections.Generic.List<Collection> GetAllSubCollections(string RootID)
        {
            List<Collection> col = GetCollectionsByParent(RootID).ToList() ;
            List<Collection> retcol = GetCollectionsByParent(RootID).ToList();

            foreach (Collection subcol in col)
            {
                retcol.AddRange(GetCollectionsByParent(subcol.CollectionID).ToList());
            }

            return retcol;
        }

    }
}